package cn.drenal.capcap.mapper;

import cn.drenal.capcap.entity.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * 统计数据Mapper接口
 */
@Mapper
public interface StatisticsMapper extends BaseMapper<User> {

    /**
     * 获取指定日期范围内每天新增用户数量
     *
     * @param startDate 开始日期
     * @param endDate   结束日期
     * @return 日期和用户数量的列表
     */
    @Select("SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, COUNT(*) as count " +
            "FROM user " +
            "WHERE create_time BETWEEN #{startDate} AND #{endDate} " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
            "ORDER BY date ASC")
    List<Map<String, Object>> getUserGrowthByDateRange(@Param("startDate") Date startDate, @Param("endDate") Date endDate);

    /**
     * 获取指定日期范围内的用户总数
     *
     * @param endDate 截止日期
     * @return 用户总数
     */
    @Select("SELECT COUNT(*) FROM user WHERE create_time <= #{endDate}")
    Long getUserCountByDate(@Param("endDate") Date endDate);

    /**
     * 获取各分类下的作品数量
     *
     * @return 分类和作品数量的列表
     */
    @Select("SELECT c.name, COUNT(a.id) as count " +
            "FROM category c " +
            "LEFT JOIN artwork a ON c.id = a.category_id " +
            "GROUP BY c.id, c.name")
    List<Map<String, Object>> getArtworkCountByCategory();
}